package birs.controller.student.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.controller.student.model.STD002Model;
import birs.controller.student.model.STD002ModelA;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STD002Task {
	public void getStudentAmount(HttpServletRequest request,HttpServletResponse response){
		List<STD002Model> std002ModelList = null;
		try (Connection con = ConnectionDB.getRegConnection()){
			ResultSetMapper<STD002Model> std002Model = new ResultSetMapper<STD002Model>();
			std002ModelList = std002Model.mapRersultSetToObject(getStudentAmountSQL(con), STD002Model.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(std002ModelList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getFirstDrilldown(HttpServletRequest request,HttpServletResponse response,String acadYear){
		List<STD002ModelA> std002ModelAList = null;
		try(Connection con = ConnectionDB.getRegConnection()){
			ResultSetMapper<STD002ModelA> std002ModelA = new ResultSetMapper<STD002ModelA>();
			std002ModelAList = std002ModelA.mapRersultSetToObject(getFirstDrilldownSQL(con, acadYear), STD002ModelA.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(std002ModelAList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private ResultSet getStudentAmountSQL(Connection con){
		String sql = " 	SELECT SM.ADMITACADYEAR AS ACADYEAR,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 2 THEN 3 ELSE 1 END) AS STUDYPERIOD,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 'ภาคปกติ' ELSE 'ภาคพิเศษ' END) AS STUDYPERIODNAME,	"
				+" 	COUNT(1) AS AMOUNT	"
				+" 	FROM STUDENTMASTER SM	"
				+" 	WHERE SM.ADMITACADYEAR > 2547	"
				+" 	AND SM.STUDYPERIOD IN (1,2)	"
				+" 	GROUP BY SM.ADMITACADYEAR,SM.STUDYPERIOD	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT SM.ADMITACADYEAR,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 2 ELSE 4 END) AS STUDYPERIOD,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 'สำเร็จการศึกษาภาคปกติ' ELSE 'สำเร็จการศึกษาภาคพิเศษ' END) AS STUDYPERIODNAME,	"
				+" 	COUNT(1) AS AMOUNT	"
				+" 	FROM STUDENTMASTER SM , STUDENTGRADUATE SG	"
				+" 	WHERE SM.STUDENTID = SG.STUDENTID	"
				+" 	AND SM.ADMITACADYEAR > 2547	"
				+" 	AND SM.STUDYPERIOD IN (1,2)	"
				+" 	AND SG.GRADUATESTATUS = 'Y'	"
				+" 	GROUP BY SM.ADMITACADYEAR,SM.STUDYPERIOD	"
				+" 	ORDER BY ACADYEAR,STUDYPERIOD ";
		return getData(sql, con);
	}
	
	private ResultSet getFirstDrilldownSQL(Connection con,String acadYear){
		String sql = " 	SELECT SM.ADMITACADYEAR,F.FACULTYID,F.FACULTYNAME,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 2 THEN 3 ELSE 1 END) AS STUDYPERIOD,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 'ภาคปกติ' ELSE 'ภาคพิเศษ' END) AS STUDYPERIODNAME,	"
				+" 	COUNT(SM.STUDENTID) AS AMOUNT	"
				+" 	FROM STUDENTMASTER SM,FACULTY F	"
				+" 	WHERE SM.FACULTYID = F.FACULTYID	"
				+" 	AND SM.ADMITACADYEAR = "+acadYear
				+" 	AND SM.STUDYPERIOD IN (1,2)	"
				+" 	GROUP BY SM.ADMITACADYEAR,F.FACULTYID,F.FACULTYNAME,SM.STUDYPERIOD	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT SM.ADMITACADYEAR,F.FACULTYID,F.FACULTYNAME,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 2 ELSE 4 END) AS STUDYPERIOD,	"
				+" 	(CASE WHEN SM.STUDYPERIOD = 1 THEN 'สำเร็จการศึกษาภาคปกติ' ELSE 'สำเร็จการศึกษาภาคพิเศษ' END) AS STUDYPERIODNAME,	"
				+" 	COUNT(1) AS AMOUNT	"
				+" 	FROM STUDENTMASTER SM , STUDENTGRADUATE SG, FACULTY F	"
				+" 	WHERE SM.STUDENTID = SG.STUDENTID	"
				+" 	AND SM.ADMITACADYEAR = "+acadYear
				+" 	AND SM.STUDYPERIOD IN (1,2)	"
				+" 	AND SM.FACULTYID = F.FACULTYID	"
				+" 	AND SG.GRADUATESTATUS = 'Y'	"
				+" 	GROUP BY SM.ADMITACADYEAR,F.FACULTYID,F.FACULTYNAME,SM.STUDYPERIOD	"
				+" 	ORDER BY FACULTYID,STUDYPERIOD ";

		return getData(sql, con);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
